package cn.iocoder.yudao.framework.excel.core.handler;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.iocoder.yudao.framework.common.core.KeyValue;
import cn.iocoder.yudao.framework.dict.core.DictFrameworkUtils;
import cn.iocoder.yudao.framework.excel.core.annotations.ExcelColumnSelect;
import cn.iocoder.yudao.framework.excel.core.function.ExcelColumnSelectFunction;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.lang.reflect.Field;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static cn.iocoder.yudao.framework.common.util.collection.CollectionUtils.convertList;

/**
 * 基于固定 sheet 实现下拉框
 *
 * @author HUIHUI
 */
@Slf4j
public class FormulaTotalHandler implements CellWriteHandler {

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (isHead) return; // 跳过表头
        //只有电站导入的时候可以设置公式
        if(writeSheetHolder.getSheetName().equals("电站导入模板") ){
            if (head != null && "lon".equals(head.getFieldName())) {
                // 获取当前行号（Excel从1开始，Java从0开始，需+1）
                int rowNum = cell.getRowIndex() + 1;

                // 设置公式
                String formula = String.format("MID(D2, 2, FIND(\",\", D2) -2 )", rowNum, rowNum);
                cell.setCellFormula(formula);
            }
            if (head != null && "lat".equals(head.getFieldName())) {
                // 获取当前行号（Excel从1开始，Java从0开始，需+1）
                int rowNum = cell.getRowIndex() + 1;

                // 设置公式
                String formula = String.format("MID(D2, FIND(\",\", D2) +2, LEN(D2) - FIND(\",\", D2) -2 )", rowNum, rowNum);
                cell.setCellFormula(formula);
            }
            // 判断当前单元格是否属于 "coordinates" 字段
            if (head != null && "coordinates".equals(head.getFieldName())) {
                // 获取当前行号（Excel从1开始，Java从0开始，需+1）
                int rowNum = cell.getRowIndex() + 1;

                // 设置公式
                String formula = String.format(" \"[\" &\n" +
                        "  \"[\" & TEXT(E2 - (10 / (111320 * COS(RADIANS(F2))) / 2), \"0.000000\") & \",\" & TEXT(F2 + (10 / 111320 / 2), \"0.000000\") & \"],\" &\n" +
                        "  \"[\" & TEXT(E2 + (10 / (111320 * COS(RADIANS(F2))) / 2), \"0.000000\") & \",\" & TEXT(F2 + (10 / 111320 / 2), \"0.000000\") & \"],\" &\n" +
                        "  \"[\" & TEXT(E2 + (10 / (111320 * COS(RADIANS(F2))) / 2), \"0.000000\") & \",\" & TEXT(F2 - (10 / 111320 / 2), \"0.000000\") & \"],\" &\n" +
                        "  \"[\" & TEXT(E2 - (10 / (111320 * COS(RADIANS(F2))) / 2), \"0.000000\") & \",\" & TEXT(F2 - (10 / 111320 / 2), \"0.000000\") & \"]\" &\n" +
                        "\"]\"", rowNum, rowNum);
                cell.setCellFormula(formula);
            }
        }
    }

}
